from urllib.parse import quote_plus
from sqlalchemy import create_engine, Column, Sequence, Integer, MetaData, String, ForeignKeyConstraint
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
from sqlalchemy.orm import sessionmaker, relationship
from pprint import pprint



connection_string = "Trusted_Connection=yes;DRIVER={SQL Server Native Client 11.0};SERVER=srvsql12beta;Database=dataware"
connection_string = quote_plus(connection_string) 
connection_string = "mssql+pyodbc:///?odbc_connect=%s" % connection_string

engine = create_engine(connection_string)
meta = MetaData(bind=engine)
Base = declarative_base()
Base.metadata = meta 
Base.metadata.reflect(only=['SCPF','SAPF', 'V5PF'])
Session = sessionmaker()
session = Session(bind=engine)
session = Session()

class V5PF(Base):
    #cls attr for alchemy to identify db table to reflect from
    __tablename__ = 'V5PF'
    #__table_args__ = (ForeignKeyConstraint(['SAAB','SAAN','SAAN'],['SCPF.SCAB','SCPF.SCAN','SCPF.SCAS'], name='sapf_fk'), {'extend_existing': True})
    #__mapper_args__ = {"primary_key": [Base.metadata.tables['SAPF'].c.SAPOD,Base.metadata.tables['SAPF'].c.SABRNM,Base.metadata.tables['SAPF'].c.SAPBR,Base.metadata.tables['SAPF'].c.SAPSQ]}
    __mapper_args__ = {"primary_key":[
            Base.metadata.tables['V5PF'].c.V5DLP, 
            Base.metadata.tables['V5PF'].c.V5BRNM,
            Base.metadata.tables['V5PF'].c.V5DLR
            ]
    }
    #def __repr__(self):
    #    return 'Transaction {}: {}'.format(self.SAPOD, self.SAAMA)

class SCPF(Base):
    #cls attr for alchemy to identify db table to reflect from
    __tablename__ = 'SCPF'
    #__table_args__ = (ForeignKeyConstraint(['SCAB','SCAN','SCAS'],['SAPF.SAAB','SAPF.SAAN','SAPF.SAAN'], name='scpf_fk'), {'extend_existing': True})
    __mapper_args__ = {"primary_key": [
    		Base.metadata.tables['SCPF'].c.SCAB,
    		Base.metadata.tables['SCPF'].c.SCAN,
    		Base.metadata.tables['SCPF'].c.SCAS
    		]
    }
    transactions = relationship('SAPF', uselist=True, primaryjoin='and_(SCPF.SCAB==SAPF.SAAB, SCPF.SCAN==SAPF.SAAN, SCPF.SCAS==SAPF.SAAS)', backref='SCPF')
    deals = relationship('V5PF', uselist=True, primaryjoin='and_(SCPF.SCAB==V5PF.V5ABD, SCPF.SCAN==V5PF.V5AND, SCPF.SCAS==V5PF.V5ASD)', backref='SCPF')
    #, primaryjoin='my_fk_test'
    def __repr__(self):
        return 'Account: {}{}{}'.format(self.SCAB, self.SCAN, self.SCAS)



class SAPF(Base):
    #cls attr for alchemy to identify db table to reflect from
    __tablename__ = 'SAPF'
    __table_args__ = (ForeignKeyConstraint(['SAAB','SAAN','SAAN'],['SCPF.SCAB','SCPF.SCAN','SCPF.SCAS'], name='sapf_fk'), {'extend_existing': True})
    __mapper_args__ = {"primary_key": [Base.metadata.tables['SAPF'].c.SAPOD,Base.metadata.tables['SAPF'].c.SABRNM,Base.metadata.tables['SAPF'].c.SAPBR,Base.metadata.tables['SAPF'].c.SAPSQ]}
    
    def __repr__(self):
        return 'Transaction {}: {}'.format(self.SAPOD, self.SAAMA)






r=session.query(SCPF).first()
pprint(r.transactions)
